library(tidyverse)
library(readxl)

election2017 = read_excel("election2017.xlsx")
election2017[is.na(election2017)] = 0
election2017 = election2017 %>%
  mutate(union      = cdu + csu,
         avgage     = (ageu18 * mean(0:18)) + (age1824 * mean(18:25)) + age2534 * mean(25:35) + age3559 * mean(35:60) + age6074 * mean(60:75) + ageo75 * 80.64,
         cdu        = cdu / votes,
         sdp        = sdp / votes,
         linke      = linke / votes,
         grune      = grune / votes,
         fdp        = fdp / votes,
         afd        = afd / votes,
         minorleft  = linke + grune,
         avged      = ((0 * noedcert) + (1 * noseced) + (2 * seccert) +
                      (3 * intcert) + (4 * univcert)) / 5,
         loggdppc   = log(gdppc),
         logdens    = log(dens))

save(election2017, file = "cleandata.Rda")